﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-28
修改:
修改时间: 
功能: 店间调拨需求表
 ******************************************************/

using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.library;
using george.Web.Helper;
using VerticaAPI.model;

namespace VerticaAPI
{
    [Description("店间调拨需求表")]
    public class DwAllocationRequireHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-03-28
        [MustLogin]
        [Description("店间调拨需求表")]
        [ReturnType(typeof(PaperData<IGrouping<string, DwAllocationRequireModel.ListModel>>))]
        public ApiResult GetDwAllocationRequire([ParamDescription("调拨日期")] DateTime? LastMoveDate,
            [ParamDescription("查询条件")] string Keys = "", [ParamDescription("类别")] string KindID = "",
            [ParamDescription("品类")] string ModelID = "", [ParamDescription("商品年份")] string YearNo = "",
            [ParamDescription("商品季节")] string SeasonName = "",
            [ParamDescription("省份")] string ProID = "",[ParamDescription("城市")] string CityID = "", 
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                var keys = "redis-server";
                //string url = ;
                if (!LastMoveDate.HasValue) return Error("请选择调拨日期");
                var count = ReturnDwAllocationRequireCount(Keys, KindID, ModelID, YearNo, SeasonName, ProID, CityID, LastMoveDate.Value);
                var result = PaperData<IGrouping<string, DwAllocationRequireModel.ListModel>>.Create(count, Size, Page);
                var dt = ReturnDwAllocationRequireList(Keys, KindID, ModelID, YearNo, SeasonName, ProID, CityID, LastMoveDate.Value, Page, Size);
                var list = ModelConvertHelper<DwAllocationRequireModel.ListModel>.ConvertToModel(dt).ToList();
                //TODO:foreach不能修改元素的值
                //list.ForEach(d => d.PicUrl = string.Format(url, d.MaterialID));
                foreach (var item in list.ToArray())
                {
                    item.PicUrl =$@"http://{IpAddressHelper.WebConfig(keys)}:8999/terminal/products/123/{item.MaterialID}.jpg";
                }
                result.Items = list.GroupBy(d => d.ShopID + "_" + d.MaterialID + "_" + d.ItemName + "_" + d.city_name).ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回店间调拨需求表信息
        /// </summary>
        /// <param name="Keys"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-28
        public DataTable ReturnDwAllocationRequireList(string Keys, string KindID, string ModelID, string YearNo, string SeasonName, string ProID, string CityID, DateTime LastMoveDate,int Page,int Size)
        {
            try
            {
                var sql = $@"SELECT  req.*
FROM    ( SELECT    *
          FROM      ( SELECT    req.ShopID ,
                                req.MaterialID ,
                                req.ItemName ,
                                req.city_name ,
                                ROW_NUMBER() OVER ( ORDER BY req.ShopID, req.MaterialID, req.ItemName ) AS Row
                      FROM      dw.dw_AllocationRequire req
                                INNER JOIN dw.dw_Shop shop ON req.ShopID = shop.ShopID
                                INNER JOIN ods.ods_erp_Bas_Area area ON shop.AreaID = area.AreaID
                                INNER JOIN goods.goods_PL_ModelItem item ON req.ItemName = item.ItemName
                                INNER JOIN ods.ods_erp_SD_Mat_Material2 mat ON req.MaterialID = mat.MaterialID
                                                              AND mat.CompanyID = '{UserInfo.UserCompanyId}'
                      WHERE     req.Allocation_Date = '{LastMoveDate:yyyy-MM-dd}'
                      {(string.IsNullOrEmpty(Keys) ? "" : $@" and (req.ShopCode like '%{Keys}%' or req.ShopName like '%{Keys}%' or req.MaterialID like '%{Keys}%') ")}
                      {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentId='{ProID}' ")}
                      {(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
                      {(string.IsNullOrEmpty(KindID) ? "" : $@" and mat.KindID='{KindID}' ")}
                      {(string.IsNullOrEmpty(ModelID) ? "" : $@" and item.ModelID='{ModelID}' ")}
                      {(string.IsNullOrEmpty(YearNo) ? "" : $@" and mat.YearNo='{YearNo}' ")}
                      {(string.IsNullOrEmpty(SeasonName) ? "" : $@" and mat.SeasonName='{SeasonName}' ")}
                      GROUP BY  req.ShopID ,
                                req.MaterialID ,
                                req.ItemName ,
                                req.city_name
                    ) table1
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        INNER JOIN ( SELECT req.*
                     FROM   dw.dw_AllocationRequire req
                            INNER JOIN dw.dw_Shop shop ON req.ShopID = shop.ShopID
                            INNER JOIN ods.ods_erp_Bas_Area area ON shop.AreaID = area.AreaID
                            INNER JOIN goods.goods_PL_ModelItem item ON req.ItemName = item.ItemName
                            INNER JOIN ods.ods_erp_SD_Mat_Material2 mat ON req.MaterialID = mat.MaterialID
                                                              AND mat.CompanyID = '{UserInfo.UserCompanyId}'
                     WHERE  req.Allocation_Date = '{LastMoveDate:yyyy-MM-dd}'
                     {(string.IsNullOrEmpty(Keys) ? "" : $@" and (req.ShopCode like '%{Keys}%' or req.ShopName like '%{Keys}%' or req.MaterialID like '%{Keys}%') ")}
                     {(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentId='{ProID}' ")}
                     {(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
                     {(string.IsNullOrEmpty(KindID) ? "" : $@" and mat.KindID='{KindID}' ")}
                     {(string.IsNullOrEmpty(ModelID) ? "" : $@" and item.ModelID='{ModelID}' ")}
                     {(string.IsNullOrEmpty(YearNo) ? "" : $@" and mat.YearNo='{YearNo}' ")}
                     {(string.IsNullOrEmpty(SeasonName) ? "" : $@" and mat.SeasonName='{SeasonName}' ")}
                   ) req ON table2.ShopID = req.ShopID
                            AND table2.MaterialID = req.MaterialID
                            AND table2.ItemName = req.ItemName
                            AND table2.city_name = req.city_name
ORDER BY req.ShopID ,
        req.MaterialID ,
        req.ItemName";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回店间调拨需求表总条数
        /// </summary>
        /// <param name="Keys"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-28
        public int ReturnDwAllocationRequireCount(string Keys, string KindID, string ModelID, string YearNo, string SeasonName, string ProID, string CityID, DateTime LastMoveDate)
        {
            try
            {
                var sql = $@"select count(1) from (
select count(1) from dw.dw_AllocationRequire req
inner join dw.dw_Shop shop on req.ShopID=shop.ShopID
inner join ods.ods_erp_Bas_Area area on shop.AreaID=area.AreaID
inner join goods.goods_PL_ModelItem item on req.ItemName=item.ItemName
inner join ods.ods_erp_SD_Mat_Material2 mat on req.MaterialID=mat.MaterialID and mat.CompanyID='{UserInfo.UserCompanyId}'
where req.Allocation_Date='{LastMoveDate:yyyy-MM-dd}'
{(string.IsNullOrEmpty(Keys) ? "" : $@" and (req.ShopCode like '%{Keys}%' or req.ShopName like '%{Keys}%' or req.MaterialID like '%{Keys}%') ")}
{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentId='{ProID}' ")}
{(string.IsNullOrEmpty(CityID) ? "" : $@" and area.AreaID='{CityID}' ")}
{(string.IsNullOrEmpty(KindID) ? "" : $@" and mat.KindID='{KindID}' ")}
{(string.IsNullOrEmpty(ModelID) ? "" : $@" and item.ModelID='{ModelID}' ")}
{(string.IsNullOrEmpty(YearNo) ? "" : $@" and mat.YearNo='{YearNo}' ")}
{(string.IsNullOrEmpty(SeasonName) ? "" : $@" and mat.SeasonName='{SeasonName}' ")}
group by req.ShopID,req.MaterialID,req.ItemName,req.city_name) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导出店间调拨需求表
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-31
        public DataTable ExportDwAllocationRequire(string Keys)
        {
            try
            {
                var sql = $@"select req.* from dw.vm_dw_AllocationRequire req
inner join dw.dw_Shop shop on req.ShopID=shop.ShopID
inner join ods.ods_erp_Bas_Area area on shop.AreaID=area.AreaID
inner join goods.goods_PL_ModelItem item on req.ItemName=item.ItemName
inner join ods.ods_erp_SD_Mat_Material2 mat on req.MaterialID=mat.MaterialID and mat.CompanyID='{UserInfo.UserCompanyId}'
where 1=1 {(string.IsNullOrEmpty(Keys) ? "" : Keys)}
order by req.ShopID,req.MaterialID,req.ItemName";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
